// 引入express
const express = require("express")
//这个组件用于接收post数据
const bodyParser = require("body-parser")
var jwt = require("jsonwebtoken")
const fs = require('fs');
const path = require('path');
const csv = require('csv-parser');
const { Readable } = require('stream'); 
// 创建服务器对象
const app = express()
const {Client} = require("pg")
const conn = new Client({
	host: "127.0.0.1",
	user: "postgres",
	password: "whuyiyulu666666",
    //此处是你添加的数据库名
	database: "animalsmigration",
	// multipleStatements: true,
	port : 5432,
})
conn.connect();

app.use(bodyParser.urlencoded({ extended: false }))
app.use(bodyParser.json())
// 配置multer
const multer = require("multer");
const upload = multer({
    storage: multer.memoryStorage(), // 文件暂存内存
    limits: {
        fileSize: 50 * 1024 * 1024 // 50MB限制
    },
	// dest: 'uploads/'
});

//解决跨域
const cors = require("cors");
app.use(cors()); 
app.all("*", function (req, res, next) {
	res.header("Access-Control-Allow-Origin", "*")
	// res.header("Access-Control-Allow-Headers", "Content-Type")
	res.header("Access-Control-Allow-Headers", "X-Requested-With,Content-Type")
	// res.header("Access-Control-Allow-Methods", "*")
	res.header("Access-Control-Allow-Methods", "PUT,POST,GET,DELETE,OPTIONS")
	res.header("Content-Type", "application/json;charset=utf-8")
	next()
})
 
// //使用jwt验证
// const{expressjwt}=require("express-jwt")
// app.use(expressjwt({secret:"secret"}).unless({path:["/login"],path:["/register"]}))



//开启监听
app.listen(4000, () => {
	console.log("4000端口已经启动。。。")
})

/////////////////////////////////注册接口////////////////////////////////
app.post("/register", (req, res) => {
	var username = req.body.username
	var password = req.body.password
	var role = req.body.role
	var register_time = new Date().toLocaleString()
	var last_login_time = new Date().toLocaleString()
	if (!username || !password||!role) {
		res.send({
			code: 400,
			msg: "用户信息填写不完整",
		})
		console.log("用户信息填写不完整")
		return
	}
	if (username && password && role) {
		const result = 'SELECT * FROM public.users WHERE username = $1'
		conn.query(result, [username], (err, results) => {
			if (err) throw err
			if (results.rows.length >= 1) {
				//如果有相同用户名，则注册失败，用户名重复
				res.send({ code: 500, msg: "注册失败，用户名重复" })
				console.log("用户名重复")
			} else {
				const sqlStr = "insert into public.users\
				(username,password,role,user_id,register_date,login_date) values\
				($1, $2, $3,nextval('users_id_seq'),$4,$5)"
				conn.query(sqlStr, [username, password,role,register_time,last_login_time], 
					(err, results) => {
					if (err) throw err
					else {
						res.send({ code: 200, msg: "注册成功" })
						console.log("注册成功")}
				})
			}
		})
	}
 
	console.log("接收成功", req.body)
})
//////////////////////////////////登录接口////////////////////////////////
app.post("/login", (req, res) => {
	var username = req.body.username
	var password = req.body.password
	if (!username || !password) {
		res.send({
			code: 400,
			msg: "用户名与密码为必传参数...",
		})
		return
	}
	const sqlStr = "select * from public.users WHERE username=$1 AND password=$2"
	conn.query(sqlStr, [username, password], (err, result) => {
		console.log(sqlStr);
		if (err) throw err
		if (result.rows.length > 0) {
			// 生成token
			console.log(result.rows[0].username);
			var token = jwt.sign(
				{
					username: result.rows[0].username,
				},
				"secret",//这是私钥
				{ expiresIn: "1h" },//有效期限
			)
			console.log(token)
			console.log(result.rows[0].role)
			data={
				token:token,
				username:username,
				userrole:result.rows[0].role,
				user_id:result.rows[0].user_id,
			}
			res.send({ code: 200, msg: "登录成功", data: data })
		} 
		else {
			res.send({ code: 500, msg: "密码错误或用户不存在" })
		}
	})
})

///////////////////////////////////获取论文接口////////////////////////////////
// app.post("knowledge_git/getpapers", (req, res) => {
// 	var animal_type = req.body.animal_type;
// 	console.log(animal_type);
// 	const sqlStr = "SELECT f.*, c.commenter_name, c.comment_text \
// 	FROM public.knowledge_files f LEFT JOIN knowledge_comments c ON f.file_id = c.file_id \
// 	WHERE f.file_type = 'pdf' AND (f.animal_species_pair->>'animal1' = $1 OR f.animal_species_pair->>'animal2' = $1)";
// 	//查询语句修改完善，希望可以通过两个动物种类中国任意一个满足查询关键词就可以
// 	conn.query(sqlStr,[animal_type],(err, result) => {
// 		//打印查询语句
// 		console.log(sqlStr);
// 		if (err) throw err
// 		if (result.rows.length > 0) {
// 			res.send({ code: 200, msg: "获取成功", data: result.rows});
// 			console.log(result.rows);
// 		} else {
// 			res.send({ code: 500, msg: "暂无数据" })
// 			console.log("暂无数据")
// 		}
// 	})
// })

app.post("/knowledge_git/getpapers", (req, res) => {
	var animal_type = req.body.animal_type;
	console.log(animal_type);
	const sqlStr = "SELECT f.file_id, f.title, f.author,f.publication_date,  f.abstract, f.publisher\
	FROM public.knowledge_files f\
	WHERE f.file_type = 'pdf' AND (f.animal_species_pair->>'animal1' = $1 OR f.animal_species_pair->>'animal2' = $1)";
	//查询语句修改完善，希望可以通过两个动物种类中国任意一个满足查询关键词就可以
	conn.query(sqlStr,[animal_type],(err, result) => {
		//打印查询语句
		console.log(sqlStr);
		if (err) throw err
		if (result.rows.length > 0) {
			res.send({ code: 200, msg: "获取成功", data: result.rows});
			console.log(result.rows);
		} else {
			res.send({ code: 500, msg: "暂无数据" })
			console.log("暂无数据")
		}
	})
})

///////////////////////////////////获取视频资料接口////////////////////////////////
app.get("/getvideos", (req, res) => {
	const sqlStr = "select * from knowledge_files where file_type='video' and animal_species_pair@>'{" + req.query.species + "}' and animal_species_pair@>'{" + req.query.species2 + "}'"
	conn.query(sqlStr, (err, result) => {
		if (err) throw err
		if (result.rows.length > 0) {
			res.send({ code: 200, msg: "获取成功", data: result.rows })
		} else {
			res.send({ code: 500, msg: "暂无数据" })
		}
	})
})



////////////////////////////////////根据id获取文件接口///////////////
// 文件获取接口
app.get('/knowledge_git/getfile/:id', async (req, res) => {
	try {
	console.log(req.params.id);
	  const result = await conn.query(
		`SELECT file_data, mime_type 
		 FROM public.knowledge_files 
		 WHERE file_id = $1`,
		[req.params.id]
	  );
	  console.log(result.rows[0].file_data);
	  //将二进制结果保存为文件txt
	  console.log('文件保存成功');
	  res.send({code: 200, msg: "文件获取成功", data: result.rows[0].file_data});
	} catch (err) {
		console.error('Error:', err); 
	  res.status(404).send('File not found');
	}
  });
  
////////////////////////////////////获取论文评论接口///////////////
app.get('/knowledge_git/getcomments/:id', async (req, res) => {
	try {
		console.log(req.params.id);
		const result = await conn.query(
			`SELECT 
			comment_id,
			user_id,
			commenter_name,
			comment_text,
			created_at,
			parent_comment_id,
			is_expert
			FROM 
			public.knowledge_comments
			WHERE 
			file_id = $1
			ORDER BY 
			created_at DESC;`,
			[req.params.id]
		);
		console.log(result.rows);
		if (result.rows.length > 0) {
			res.send({ code: 200, msg: "讨论获取成功", data: result.rows });
		}
		else {
			res.send({ code: 500, msg: "暂无讨论，快来发一条吧" });
		}
		
	} catch (err) {		
		console.error('Error:', err); 
		res.status(404).send({ code: 500, msg: "出错了，请稍后再试" });
	}
});

////////////////////////////////////发表评论接口/////////////////////
app.post('/knowledge_git/addcomment', async (req, res) => {
	try {
		const { file_id, user_id, commenter_name, comment_text, parent_comment_id,is_expert } = req.body;
		const result = await conn.query(
			`INSERT INTO public.knowledge_comments 
			(file_id, user_id, commenter_name, comment_text, parent_comment_id,is_expert)
			VALUES ($1, $2, $3, $4, $5, $6)
			RETURNING comment_id`,
			[file_id, user_id, commenter_name, comment_text, parent_comment_id,is_expert]
		);
		console.log(result.rows[0].comment_id);
		res.send({ code: 200, msg: "评论发表成功" });
	} catch (err) {
		console.error('Error:', err); 
		res.status(404).send({ code: 500, msg: "出错了，请稍后再试" });
	}
});
////////////////////////////////////获取论文数量接口///////////////
app.get('/knowledge_git/getpapercount', async (req, res) => {
	try {
		const result = await conn.query(
			`SELECT COUNT(*) as count FROM public.knowledge_files WHERE file_type = 'pdf'`
		);
		console.log(result.rows[0].count);
		res.send({ code: 200, msg: "获取成功", data: result.rows[0].count });
	} catch (err) {
		console.error('Error:', err); 
		res.status(404).send({ code: 500, msg: "出错了，请稍后再试" });
	}
	
});
////////////////////////////////////获取评论数量接口///////////////
app.get('/knowledge_git/getcommentcount', async (req, res) => {
	try {
		const result = await conn.query(
			`SELECT COUNT(*) as count FROM public.knowledge_comments`
		);
		console.log(result.rows[0].count);
		res.send({ code: 200, msg: "获取成功", data: result.rows[0].count });
	} catch (err) {
		console.error('Error:', err); 
		res.status(404).send({ code: 500, msg: "出错了，请稍后再试" });
	}
	
});
////////////////////////////////////获取二维动物轨迹接口//		
app.get('/2Dtrack/get_track/:species', async (req, res) => {
	const species = req.params.species;
  
	try {
	  const query = `
		SELECT 
		  event_id, 
		  time, 
		  location_long, 
		  location_lat, 
		  sensor_type, 
		  study_name, 
		  stage, 
		  activity 
		FROM 
		  animals_track
		WHERE 
		  species = $1
		ORDER BY 
		  event_id;
	  `;
  
	  const result = await conn.query(query, [species]);
  
	  if (result.rows.length === 0) {
		return res.status(404).json({ error: 'No data found for this species' });
	  }
  
	  // 构建轨迹线
	  const coordinates = result.rows.map(row => [
		parseFloat(row.location_long),
		parseFloat(row.location_lat)
	  ]);
  
	  // 构建线Feature
	  const lineFeature = {
		type: 'Feature',
		geometry: {
		  type: 'LineString',
		  coordinates: coordinates
		},
		properties: {
		  species: species,
		  start_time: result.rows[0].time,
		  end_time: result.rows[result.rows.length - 1].time
		}
	  };
  
	  // 构建点Feature集合
	  const pointFeatures = result.rows.map(row => ({
		type: 'Feature',
		geometry: {
		  type: 'Point',
		  coordinates: [
			parseFloat(row.location_long),
			parseFloat(row.location_lat)
		  ]
		},
		properties: {
		  event_id: row.event_id,
		  time: row.time,
		  sensor_type: row.sensor_type,
		  study_name: row.study_name,
		  stage: row.stage,
		  activity: row.activity
		}
	  }));
  
	  // 构建响应
	  const geojsonResponse = {
		type: 'FeatureCollection',
		features: [lineFeature, ...pointFeatures]
	  };
  
	  res.json({code: 200, msg: "获取成功", data: geojsonResponse});
	} catch (error) {
	  console.error('Error fetching data:', error);
	  res.status(500).json({ error: 'Failed to fetch data' });
	}
  });
  /////////获取栅格数据接口/////////////
app.get('/2Dtrack/get_raster/:height/:time/:value', async (req, res) => {
	const height = parseInt(req.params.height, 10);
	const time=req.params.time;
	const value = req.params.value;
	try {
	  const tiff = await getRasterTIFF(height,time,value);
	  if (!tiff) return res.status(404).send('Not found');
	  res.type('image/tiff').send(tiff);
	  console.log(tiff);
	} catch (err) {
	  console.error(err);
	  res.status(500).send('Server error');
	}
  });
  async function getRasterTIFF(height,time,value) {
	filename='data_'+value+'_'+height+'hpa_'+time+'.tif';
	const sql = `
	  SELECT ST_AsTIFF(rast) AS tiff
	  FROM public.raster_data
	  WHERE height = $1 and DATE(time) = $2 and filename = $3
	`;
	const res = await conn.query(sql, [height,time,filename]);
	return res.rows[0]?.tiff || null;
  }

  ////////////////////////////////////获取动物轨迹数量接口///////////////
  app.get('/knowledge_git/get_track_count', async (req, res) => {
	try {
	  const result = await conn.query(
		`SELECT COUNT(DISTINCT species) as count FROM public.animals_track`
	  );
	  console.log(result.rows[0].count);
	  res.send({ code: 200, msg: "获取成功", data: result.rows[0].count });
	} catch (err) {
	  console.error('Error:', err); 
	  res.status(404).send({ code: 500, msg: "出错了，请稍后再试" });
	}
	
  });
  ////////////////////////////////////获取栅格数据数量接口///////////////
  app.get('/knowledge_git/get_raster_count', async (req, res) => {
	try {
	  const result = await conn.query(
		`SELECT COUNT(*) as count FROM public.raster_data`
	  );
	  console.log(result.rows[0].count);
	  res.send({ code: 200, msg: "获取成功", data: result.rows[0].count });
	} catch (err) {
	  console.error('Error:', err); 
	  res.status(404).send({ code: 500, msg: "出错了，请稍后再试" });
	}
	
  });
  //////////////////////////////////获取栅格数据时间接口///////////////
  app.get('/knowledge_git/get_raster_time', async (req, res) => {	
	try {
	  const result = await conn.query(
		`SELECT DISTINCT DATE(time) as time FROM public.raster_data ORDER BY time DESC`
	  );
	  //通过时间中的年份来分组
	  const groupByTime = {};
	  result.rows.forEach(row => {
		const year = row.time.getFullYear();
		if (!groupByTime[year]) groupByTime[year] = [];
		groupByTime[year].push(row.time);
	  });
	  //最后返回组数
	  const timeArr = Object.keys(groupByTime).map(year => ({ year, time: groupByTime[year] }));
	  console.log(timeArr);
	  res.send({ code: 200, msg: "获取成功", data: timeArr.length});
	} catch (err) {
	  console.error('Error:', err); 
	  res.status(404).send({ code: 500, msg: "出错了，请稍后再试" });
	}
	
  });
  app.get('/knowledge_git/get_online_users', async (req, res) => {	
	try {
	  const result = await conn.query(
		`SELECT COUNT(*) as count 
		FROM public.users
		WHERE login_date >= NOW()::timestamp - interval '30 minutes';`
	  );
	  console.log(result.rows[0].count);
	  res.send({ code: 200, msg: "获取成功", data: result.rows[0].count});
	} catch (err) {
	  console.error('Error:', err); 
	  res.status(404).send({ code: 500, msg: "出错了，请稍后再试" });
	}
	
  });
  app.get('/knowledge_git/get_expert_users', async (req, res) => {
	try {
		const result = await conn.query(
		`SELECT COUNT(*) as count 
		FROM public.users
		WHERE role = 2 AND authentication=true;`
	  );
	  console.log(result.rows[0].count);
	  res.send({ code: 200, msg: "获取成功", data: result.rows[0].count});
	}catch (err) {
		console.error('Error:', err); 
	  res.status(404).send({ code: 500, msg: "出错了，请稍后再试" });
	}
	
  });

///////////////////////////获取所有物种位置的接口///////////////////
app.get('/knowledge_git/get_track_points', async (req, res) => {
	try {
		const result = await conn.query(
		`SELECT species, location_long, location_lat, species_category as category_type
		FROM public.animals_track`
	  );
	  //按照species分组
	  const groupBySpecies = {};
	  result.rows.forEach(row => {
		if (!groupBySpecies[row.species]) groupBySpecies[row.species] = [];
		groupBySpecies[row.species].push([row.location_long, row.location_lat]);
		//添加species的物种category属性
		groupBySpecies[row.species].category = row.category_type;
	  });
	  //根据每个物种的点找到每个物种中心点（算平均值）
	  const centerPoints = {};
	  Object.keys(groupBySpecies).forEach(species => {
		const points = groupBySpecies[species];
		const x = points.map(p => p[0]).reduce((acc, cur) => acc + cur, 0) / points.length;
		const y = points.map(p => p[1]).reduce((acc, cur) => acc + cur, 0) / points.length;
		centerPoints[species] = [x, y];
	  });
	  const speciesArr = Object.keys(centerPoints).map(species => ({ species, center: 
		centerPoints[species], category: groupBySpecies[species].category}));
	  res.send({ code: 200, msg: "获取成功", data: speciesArr});
	} catch (err) {
		console.error('Error:', err); 
	  res.status(404).send({ code: 500, msg: "出错了，请稍后再试" });
	}
	
  });
  ///////////////////////////获取所有和物种轨迹重叠的栅格数据接口///
  app.get('/knowledge_git/get_raster_overlap/:species/:height/:value', async (req, res) => {
	try {
		const species = req.params.species;
		const height = parseInt(req.params.height, 10);
		const value = req.params.value;
		const result = await conn.query(
		`SELECT 
		at.species,
		at.event_id,
		at.time,
		at.location_long,
		at.location_lat,
		rd.height,
		ST_Value(rd.rast, at.geom) as raster_value
	FROM 
		public.animals_track at
	JOIN 
		public.raster_data rd
	ON 
		ST_Intersects(at.geom, rd.rast)
	WHERE 
		at.species = '${species}'  -- 替换为目标物种名称
		AND rd.height = ${height}
		AND DATE_PART('year', rd.time) = 2018  -- 筛选 2018 年的记录
		AND rd.filename LIKE '%${value}%'  -- 使用 LIKE 来检查文件名是否包含 'humidity'
		AND DATE_PART('month', rd.time) = DATE_PART('month', english_month_to_date(at.time));`
	  );
	  //按照event_id排序输出
	  //输出服务器返回消息的时间戳
	  console.log(new Date());
	  console.log(result.rows[0].species);
	  console.log(result.rows);
	  res.send({ code: 200, msg: "获取成功", data: result.rows});
	  
	} catch (err) {
		console.error('Error:', err); 
	  res.status(404).send({ code: 500, msg: "出错了，请稍后再试" });
	}
	
  });

///////////////////////////上传最佳拍照点位接口////////////////////
app.post('/knowledge_git/upload_best_photo_point', upload.single('photo'), async (req, res) => {
	try {
		const { species, location_long, location_lat } = req.body;
		const photoBuffer = req.file.buffer;

		// 验证坐标有效性
		const longitude = parseFloat(location_long);
		const latitude = parseFloat(location_lat);
		if (isNaN(longitude) || isNaN(latitude)) {
			throw new Error('无效的坐标格式');
		}
		
		// 插入数据库
		const result = await conn.query(
			`INSERT INTO photography_points 
			(species_name, location, photo)
			VALUES ($1, ST_SetSRID(ST_MakePoint($2, $3), 4326), $4)
			RETURNING id`,
			[species, longitude, latitude, photoBuffer]
		);
		console.log('上传最佳拍照点位成功', result.rows[0].id);
		res.json({ 
			code: 200, 
			msg: "上传成功",
			id: result.rows[0].id 
		});
	  
	} catch (err) {
	  console.error('Database Error:', err);
	  res.status(500).send({  // 服务器错误使用500状态码
		code: 500,
		msg: `上传失败：${err.message}`
	  });
	}
  });


/////////////////////////////获取3D轨迹///////////////////
  app.get('/3Dtrack/get_track/:species', async (req, res) => {
	const species = req.params.species;
  
	try {
	  // 查询数据库
	  const result = await conn.query(
		'SELECT species, event_id, "time", location_long, location_lat, elevation, sensor_type, study_name, stage, activity, ST_AsGeoJSON(geom) as geometry, species_category FROM public.animals_track_3d WHERE species = $1',
		[species]
	  );
  
	  // 将查询结果转换为GeoJSON格式
	  const geojson = {
		type: 'FeatureCollection',
		features: result.rows.map(row => ({
		  type: 'Feature',
		  geometry: JSON.parse(row.geometry),
		  properties: {
			species: row.species,
			event_id: row.event_id,
			time: row.time,
			location_long: row.location_long,
			location_lat: row.location_lat,
			elevation: row.elevation,
			sensor_type: row.sensor_type,
			study_name: row.study_name,
			stage: row.stage,
			activity: row.activity,
			species_category: row.species_category
		  }
		}))
	  };
	  console.log("三维数据获取成功:",geojson);
  
	  res.json(geojson);
	} catch (err) {
	  console.error('Error fetching data:', err);
	  res.status(500).json({ error: 'Internal server error' });
	}
  });

  ///////////////////////////获取点位和图片信息///////////////
  app.get('/2D/get_points/:speciesName', async (req, res) => {
	try {
	  const { speciesName } = req.params;
  
	  const query = `
		SELECT
		  id,
		  species_name,
		  ST_AsGeoJSON(location) as location,
		  photo,
		  created_at
		FROM public.photography_points
		WHERE species_name = $1;
	  `;
  
	  const result = await conn.query(query, [speciesName]);
  
	  if (result.rows.length === 0) {
		return res.status(404).json({
		  message: `No records found for species: ${speciesName}`,
		});
	  }
  
	  // 将结果转换为适当格式
	  const photographyPoints = result.rows.map(row => {
		return {
		  id: row.id,
		  speciesName: row.species_name,
		  location: JSON.parse(row.location), // 解析GeoJSON字符串
		  photo: row.photo, // 二进制图片数据
		  createdAt: row.created_at,
		};
	  });
  
	  res.status(200).json({
		message: `Found ${photographyPoints.length} records for species: ${speciesName}`,
		data: photographyPoints,
	  });
	  console.log('最佳拍照点位获取成功',photographyPoints);
	} catch (error) {
	  console.error('Error fetching photography points:', error);
	  res.status(500).json({
		message: 'Server error',
		error: error.message,
	  });
	}
  });
/////////////////////////用户上传动物轨迹接口///////////////////
app.post('/upload-csv/:speciesName', upload.single('csvFile'), async (req, res) => {
	try {
	  const file = req.file;
	  const speciesName = req.params.speciesName;
  
	  if (!file) {
		return res.status(400).json({ message: '未找到 CSV 文件' });
	  }
  
	  if (!file.buffer) {
		return res.status(400).json({ message: '文件缓冲区无效' });
	  }
  
	  const results = [];
  
	  // 从 Buffer 创建可读流
	  const readStream = new Readable();
	  readStream.push(file.buffer);
	  readStream.push(null); // 标示流的结束
  
	  readStream
		.pipe(csv())
		.on('data', (data) => {
			//转化data的时间格式
			//由中文转化为英文
			const monthMap = {
				'1月': 'January',
				'2月': 'February',
				'3月': 'March',
				'4月': 'April',
				'5月': 'May',
				'6月': 'June',
				'7月': 'July',
				'8月': 'August',
				'9月': 'September',
				'10月': 'October',
				'11月': 'November',
				'12月': 'December'
			};

		  results.push({
			species: 'unknown_' + speciesName,
			event_id: parseInt(data['event-id']),
			time:  monthMap[data.time] || data.time,
			location_long: parseFloat(data['location-long']),
			location_lat: parseFloat(data['location-lat']),
			sensor_type: data['sensor-type'],
			study_name: data['study-name'],
			stage: data.stage,
			activity: data.activity
		  });
		})
		.on('end', async () => {
		  const query = `
			INSERT INTO animals_track 
			(species, event_id, time, location_long, location_lat, 
				sensor_type, study_name, stage, activity, geom)
			VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, ST_SetSRID(ST_MakePoint($4, $5), 4326))
			ON CONFLICT (species, event_id) DO NOTHING`;
  
		  for (const row of results) {
			await conn.query(query, [
			  row.species,
			  row.event_id,
			  row.time,
			  row.location_long,
			  row.location_lat,
			  row.sensor_type,
			  row.study_name,
			  row.stage,
			  row.activity
			]);
		  }
		  console.log('轨迹数据导入成功！');
		  res.json({ message: '数据导入成功！', count: results.length, data: results  });
		});
  
	} catch (error) {
	  console.error('错误:', error);
	  res.status(500).json({ message: '导入失败', error: error.message });
	}
  });
  /////////////////////////用户获取视频数据接口//////////////////
  app.get('/3Dtrack/get_video/:species', async (req, res) => {
	const species = req.params.species;
  
	try {
	  const result = await conn.query(
		`SELECT file_name, file_data FROM media_files WHERE animal_tags->>'animal1' = $1 OR animal_tags->>'animal2' = $1`,
		[species]
	  );
  
	  if (result.rows.length === 0) {
		return res.status(404).send('文件不存在');
	  }
  
	  const { file_name, file_data } = result.rows[0];
  
	  // 设置响应的 Content-Type 为视频类型，根据实际文件类型调整
	  res.setHeader('Content-Type', 'video/mp4'); // 这里假设是 mp4 格式
  
	  // 直接返回文件数据到前端
	  res.send(file_data);
	} catch (err) {
	  console.error('读取失败:', err.message);
	  res.status(500).send('服务器错误');
	}
  });
  
